with t_package_full as (
    select tab_bagging.waybill_no                                                                  as billcode,
           package_code,
           scan_time,
           tab_flight.flight_num,                                                                                   -- 芯片号
           -- 这里的时间和网点是保持一致的
           coalesce(tab_deliver.scantime, tab_aging_sign.scantime,
                    '2999-12-31 23:59:59')                                                         as deliver_time, -- 出仓或者签收扫描时间（优先出仓）
           if(tab_deliver.scantime is null, tab_aging_sign.scansitecode, tab_deliver.scansitecode) as deliver_sitecode
    from (
             select tab_bagging.waybill_no, tab_bagging.package_code, tab_bagging.scan_time
             from (
                      select waybill_no, package_code, scan_time
                      from jms_dwd.dwd_tab_barscan_bagging_base_dt
                      where dt between date_add('2022-12-14', -2) and '2022-12-14'
                        and substr(package_code, 1, 2) in ('B4', 'B7', 'B8', 'B9')
                        and left (scan_time
                          , 10) = date_add('2022-12-14'
                          , -2) -- T - 3 ~ T -1的建包扫描中属于T - 3当天的扫描
                  ) tab_bagging
                      left anti
                      join -- 为了剔除属于T - 4当天跨天的建包扫描
                 (
                     select package_code
                     from jms_dwd.dwd_tab_barscan_bagging_base_dt
                     where dt = date_format(date_add(date_add('2022-12-14', -2), -1), 'yyyy-MM-dd')
                       and substr(package_code, 1, 2) in ('B4', 'B7', 'B8', 'B9')
                       and left (scan_time
                         , 10) = date_format(date_add(date_add('2022-12-14'
                         , -2)
                         , -1)
                         , 'yyyy-MM-dd')
                     group by package_code
                 ) package_pre on tab_bagging.package_code = package_pre.package_code

             union all

             select tab_bagging.waybill_no, tab_bagging.package_code, tab_bagging.scan_time
             from (
                      select waybill_no, package_code, scan_time
                      from jms_dwd.dwd_tab_barscan_bagging_base_dt
                      where dt between date_add('2022-12-14', -2) and '2022-12-14'
                        and substr(package_code, 1, 2) in ('B4', 'B7', 'B8', 'B9')
                        and left (scan_time
                          , 10) = date_add('2022-12-14'
                          , -2) -- T - 3 ~ T -1的建包扫描中属于T - 3当天的扫描
                  ) tab_bagging
                      join -- 为了得到T - 3跨到T - 2的建包扫描
                 (
                     select package_code
                     from jms_dwd.dwd_tab_barscan_bagging_base_dt
                     where dt = date_format(date_add(date_add('2022-12-14', -2), 1), 'yyyy-MM-dd')
                       and substr(package_code, 1, 2) in ('B4', 'B7', 'B8', 'B9')
                       and left (scan_time
                         , 10) = date_format(date_add(date_add('2022-12-14'
                         , -2)
                         , 1)
                         , 'yyyy-MM-dd')
                     group by package_code
                 ) package_pre on tab_bagging.package_code = package_pre.package_code
         ) tab_bagging
             left join -- 这里的芯片号只存在于当waybill_no为包号时
        (
            select waybill_no, max(flight_num) as flight_num
            from jms_dwd.dwd_tab_barscan_bagging_base_dt
            where dt between date_format(date_add(date_add('2022-12-14', -2), -1), 'yyyy-MM-dd') and '2022-12-14'
              and substr(waybill_no, 1, 2) in ('B4', 'B7', 'B8', 'B9')
            group by waybill_no
        ) tab_flight on tab_flight.waybill_no = tab_bagging.package_code
             left join -- 出仓扫描
        (
            select billcode, scantime_early as scantime, scansitecode_early as scansitecode
            from jms_dwd.dwd_barscan_deliver_dt
            where dt between date_add('2022-12-14', -2) and '2022-12-14'
              and dimtype = 'bill_scansite'
        ) tab_deliver on tab_bagging.waybill_no = tab_deliver.billcode
             left join -- 签收扫描
        (
            select billcode, scantime, scansitecode
            from jms_dws.dws_actual_aging_sign_dt
            where dt between date_add('2022-12-14', -2) and '2022-12-14'
              and tabletype = 'aging_sign'
        ) tab_aging_sign on tab_bagging.waybill_no = tab_aging_sign.billcode
),

--获取最早的出仓或签收时间以及建包时间
     t_package_unique as (
         select package_full.package_code,
                billcode,
                scan_time,
                deliver_time,
                deliver_sitecode,
                flight_num,
                center_code as unloading_destcode,
                center_name as unloading_destname,
                package_num
         from (
                  select package_code, billcode, scan_time, deliver_time, deliver_sitecode, flight_num, rank_number
                  from (
                           select package_code
                                , billcode
                                , min(scan_time) over (partition by package_code) as                scan_time -- 取得最早的建包时间
                                , deliver_time
                                , deliver_sitecode
                                , flight_num
                                , row_number()
                                   over (partition by package_code order by deliver_time,scan_time) rank_number
                           from t_package_full
                       )
                  where rank_number = 1
              ) package_full
                  left join -- 为了得到拆包目的地
             (
                 select *
                 from (
                          -- 二段码包牌记录
                          select package_number as                                                       packagecode,
                                 center_name, -- 拆包网点名称
                                 center_code, -- 拆包网点编码
                                 used_time,
                                 row_number() over (partition by package_number order by used_time desc) rnk
                          from jms_dwd.dwd_second_package_list_base_dt
                          where dt >= date_add('2022-12-14', -30)
                            and dt <= '2022-12-14'
                            and substr(package_number, 1, 2) in ('B4', 'B7', 'B8', 'B9')
                      ) a
                 where a.rnk = 1
             ) ele_package on package_full.package_code = ele_package.packagecode
                  left join -- 得到包内件数
             (
                 select package_code, count(1) as package_num
                 from (
                          select billcode, package_code from t_package_full group by billcode, package_code
                      ) package_billcode_detail
                 group by package_code
             ) package_billcode_unique on package_full.package_code = package_billcode_unique.package_code
     ),

     t_package_info as
         (
             select package_unique.package_code                                        as packagecode,
                    package_unique.scan_time                                           as bagging_time,     --建包时间
                    -- 这里的时间和网点也是保持一致的
                    coalesce(tab_arrival.scantime_early, tab_unloading.scantime_early) as unloading_time,   -- 拆包或到件扫描时间（优先到件扫描时间）
                    unloading_destcode,                                                                     --拆包目的地网点编码
                    unloading_destname,                                                                     --拆包目的地网点名称
                    package_num,
                    if(tab_arrival.scantime_early is null, tab_unloading.scansitecode_early,
                       tab_unloading.scansitecode_early)                               as unloading_sitecode,
                    package_unique.deliver_time,                                                            --出仓签收时间
                    package_unique.deliver_sitecode,                                                        --出仓签收网点
                    tab_arrival.scantime_early                                         as arrival_scantime, --到件时间
                    flight_num                                                                              --芯片号
             from t_package_unique package_unique
                      left join --取拆包表的最早扫描时间
                 (
                     select packagecode_early, scantime_early, scansitecode_early
                     from (
                              select packagecode_early,
                                     scantime_early,
                                     scansitecode_early,
                                     row_number()
                                             over (partition by packagecode_early order by scantime_early) rank_number
                              from jms_dwd.dwd_barscan_unloading_dt
                              where dt between date_add('2022-12-14', -2) and '2022-12-14'
                                and dimtype = 'bill_scansite'
                                and substr(packagecode_early, 1, 2) in ('B4', 'B7', 'B8', 'B9')
                          ) tab_unloading_full
                     where rank_number = 1
                 ) tab_unloading
                                on package_unique.package_code = tab_unloading.packagecode_early and
                                   package_unique.unloading_destcode = tab_unloading.scansitecode_early
                      left join --取到件表的扫描时间
                 (
                     select billcode, scantime_early, scansitecode_early
                     from jms_dwd.dwd_barscan_arrival_dt
                     where dt between date_add('2022-12-14', -2) and '2022-12-14'
                       and dimtype = 'bill_scansite'
                       and substr(billcode, 1, 2) in ('B4', 'B7', 'B8', 'B9')
                 ) tab_arrival
                                on package_unique.package_code = tab_arrival.billcode and
                                   package_unique.unloading_destcode = tab_arrival.scansitecode_early
         )


-- 这里拆包时间应该是unloading_time和deliver_time的最小值
insert
overwrite
table
bidefault.dm_unloading_statistics_dt
partition
(
dt
)
select to_date(unloading_time) as unloading_date,      --拆包日期
       sys_manage_region.code  as manage_regional_code,-- 管理大区编码
       sys_manage_region.name  as manage_regional_name,-- 管理大区名称
       network_detail.agent_code,                      --代理区code
       network_detail.agent_name,                      --代理区名称
       package_info.unloading_sitecode,                --拆包网点编码
       network_detail.name     as unloading_sitename,  --拆包网点名称
       unloading_destcode,                             --拆包目的地网点编码
       unloading_destname,                             --拆包目的地网点名称
       packagecode,                                    --包号
       bagging_time,                                   --建包时间
       unloading_time,                                 --拆包时间
       arrival_scantime,                               --到件时间
       package_num,                                    --包内件数
       flight_num,                                     --芯片号
       to_date(unloading_time) as dt
from (
         select if(unloading_time is null, deliver_sitecode, unloading_sitecode) as unloading_sitecode, --拆包网点编码（和时间保持一致）
                packagecode,                                                                            --包号
                bagging_time,                                                                           --建包时间
                coalesce(unloading_time, deliver_time)                           as unloading_time,     --拆包时间(取最小时间)
                unloading_destcode,
                unloading_destname,
                arrival_scantime,                                                                       --到件时间
                package_num,                                                                            --包内件数
                flight_num                                                                              --芯片号
         from t_package_info
     ) package_info
         left join
     (
         select *
         from jms_dim.dim_sys_network_detail_dt
         where dt = '2022-12-14'
     ) network_detail on package_info.unloading_sitecode = network_detail.code
         left join
     (
         select code, name, proxy_network_id
         from jms_dim.dim_sys_manage_region
     ) sys_manage_region on network_detail.agent_id = sys_manage_region.proxy_network_id
where to_date(unloading_time) between date_add('2022-12-14', -2) and '2022-12-14'
  and package_info.unloading_sitecode is not null
  and package_info.unloading_sitecode <> ''